package com.example.studentgrades.dao;



import com.example.studentgrades.model.Student;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class StudentDAO {
    private Connection connection;

    // 构造方法
    public StudentDAO(Connection connection) {
        this.connection = connection;
    }

    // 添加学生
    public void addStudent(Student student) throws SQLException {
        String sql = "INSERT INTO students (username, password) VALUES (?, ?)";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, student.getUsername());
            ps.setString(2, student.getPassword());
            ps.executeUpdate();
        }
    }

    // 根据用户名和密码查找学生
    public Student findStudentByUsernameAndPassword(String username, String password) throws SQLException {
        String sql = "SELECT * FROM students WHERE username = ? AND password = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, username);
            ps.setString(2, password);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return extractStudentFromResultSet(rs);
                }
            }
        }
        return null;
    }

    // 根据ID查找学生
    public Student findStudentById(int studentId) throws SQLException {
        String sql = "SELECT * FROM students WHERE student_id = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, studentId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return extractStudentFromResultSet(rs);
                }
            }
        }
        return null;
    }

    // 更新学生信息
    public void updateStudent(Student student) throws SQLException {
        String sql = "UPDATE students SET username = ?, password = ? WHERE student_id = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, student.getUsername());
            ps.setString(2, student.getPassword());
            ps.setInt(3, student.getStudentId());
            ps.executeUpdate();
        }
    }

    // 删除学生
    public void deleteStudent(int studentId) throws SQLException {
        String preSql = "DELETE FROM scores WHERE student_id = ?";
        try (PreparedStatement ps = connection.prepareStatement(preSql)) {
            ps.setInt(1, studentId);
            ps.executeUpdate();
        }
        String sql = "DELETE FROM students WHERE student_id = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, studentId);
            ps.executeUpdate();
        }
    }

    // 查找所有学生
    public List<Student> findAllStudents() throws SQLException {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM students";
        try (PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                students.add(extractStudentFromResultSet(rs));
            }
        }
        return students;
    }

    // 提取学生信息
    private Student extractStudentFromResultSet(ResultSet rs) throws SQLException {
        Student student = new Student();
        student.setStudentId(rs.getInt("student_id"));
        student.setUsername(rs.getString("username"));
        student.setPassword(rs.getString("password"));
        return student;
    }

}
